CREATE OR REPLACE FUNCTION create_corpora(integer) RETURNS VOID AS '
DECLARE 
  corpusSize ALIAS FOR $1;
  cur CURSOR FOR SELECT doc_id, substring(doc_url from (char_length(doc_url) - 13) for 10) FROM t_document ORDER BY 2;
  i INTEGER = 0;
  j INTEGER = 1;
  v_corp_id BIGINT;
  v_lr_id BIGINT;
  corpus_lr_type INTEGER;
  p_usr_id INTEGER;
  p_grp_id INTEGER;
  p_lr_name CHARACTER VARYING(50);
  v_doc_id INTEGER;
  v_doc_name CHARACTER VARYING(20);
  v_firstdoc_name CHARACTER VARYING(20);
  v_lastdoc_name CHARACTER VARYING(20);
BEGIN

    -- Delete previously created corpora first.
    DELETE FROM t_corpus_document 
    WHERE cd_corp_id IN (
        SELECT c.corp_id FROM t_corpus c, t_lang_resource r
        WHERE c.corp_lr_id = r.lr_id AND r.lr_name LIKE ''Corpus%'');
    DELETE FROM t_corpus
    WHERE corp_lr_id IN (SELECT r.lr_id FROM t_lang_resource r
        WHERE r.lr_name LIKE ''Corpus%'');
    DELETE FROM t_lang_resource WHERE lr_name LIKE ''Corpus%'';

    -- Initialize some ids from catalogs
    SELECT lrtp_id INTO corpus_lr_type FROM t_lr_type WHERE lrtp_type LIKE ''%Corpus%'';
    SELECT grp_id INTO p_grp_id FROM t_group WHERE grp_name LIKE ''USERS'';
    SELECT ugrp_user_id INTO p_usr_id FROM t_user_group WHERE ugrp_group_id = p_grp_id LIMIT 1;

    v_lastdoc_name := '';
    v_doc_name := '';
    v_lr_id := -1;
    OPEN cur;
    LOOP
        v_lastdoc_name := v_doc_name;
        FETCH cur INTO v_doc_id, v_doc_name;
        IF  NOT FOUND THEN
            EXIT;  -- exit loop
        END IF;
        
        IF i % corpusSize = 0 THEN

            p_lr_name := ''Corpus from '' || v_doc_name || '' to ?'';
            INSERT into t_lang_resource(lr_id,
                lr_type_id,
                lr_owner_user_id,
                lr_locking_user_id,
                lr_owner_group_id,
                lr_name,
                lr_access_mode,
                lr_parent_id)
            VALUES (nextval(''seq_lang_resource''),
                corpus_lr_type,
                p_usr_id,
                null,
                p_grp_id,
                p_lr_name,
                1,
                null);


            UPDATE t_lang_resource 
                SET lr_name = ''Corpus from '' || v_firstdoc_name || '' to '' || v_lastdoc_name
                WHERE lr_id = v_lr_id;
            
            v_firstdoc_name := v_doc_name;
            
            /* get ID */
            SELECT currval(''seq_lang_resource'')  INTO v_lr_id;

            /* Create the corpus */
            v_corp_id := persist_create_corpus(v_lr_id);

            j := j + 1;
        END IF;
        INSERT INTO t_corpus_document(cd_id,
                                       cd_corp_id,
                                       cd_doc_id)
        VALUES (nextval(''seq_corpus_document''),
            v_corp_id,
            v_doc_id);
        i := i + 1;
    END LOOP;
    CLOSE cur;
    
    UPDATE t_lang_resource 
        SET lr_name = ''Corpus from '' || v_firstdoc_name || '' to '' || v_lastdoc_name
        WHERE lr_id = v_lr_id;
END;
' LANGUAGE 'plpgsql';

-- SELECT create_corpora(1000);